﻿CREATE PROCEDURE [dbo].[utl_CreateAuditTrigger]
@Table [sysname]=NULL, @Force BIT=0
AS
BEGIN
	-- modified 12/18/2005 to let the trigger work with tables with blobs
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
		
	DECLARE @SQL           NVARCHAR(MAX)
	DECLARE @Message       VARCHAR(MAX)
	DECLARE @ErrorCode     INT
	DECLARE @RowsAffected  INT
	DECLARE @ColumnList    VARCHAR(MAX)
	
	DECLARE @CRLF VARCHAR(2); SET @CRLF = CHAR(13) + CHAR(10)
	
	DECLARE @Columns TABLE (
	            ID INTEGER IDENTITY(1, 1) PRIMARY KEY,
	            ColumnName sysname,
	            QuotedColumnName sysname,
	            VariableColumnName sysname,
	            Datatype sysname,
	            MaxLength INT,
	            IsIdentity BIT,
	            IsTimestamp BIT,
	            IsComputed BIT,
	            IsPartOfPrimaryKey BIT
	        )

	-- check for table name passed
	IF @Table IS NULL
	BEGIN
	    SET @Message = 'Must provide a table name.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 1
	END
	
	-- check for table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 2
	END
	
	-- check for audit table exists
	IF dbo.fn_TableExists('Audit') = @False
	BEGIN
	    SET @Message = 'Audit Table not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	-- check for trigger exists
	IF dbo.fn_TriggerExists(@Table, @Table + '_Audit_InsUpdDelTrg') = @True
	BEGIN
	    IF @Force = @True
	    BEGIN
	        SET @SQL = 'DROP TRIGGER ' + @Table + '_Audit_InsUpdDelTrg'
	        EXEC (@SQL) --EXECUTE sp_executesql @SQL
	        
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Trigger ''' + @Table + '_Audit_InsUpdDelTrg'' could not be dropped.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 4
	        END
	    END
	    ELSE
	    BEGIN
	        SET @Message = 'Trigger ''' + @Table + '_Audit_InsUpdDelTrg'' already exists.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 5
	    END
	END
	
	-------------------------------------------------------------------------------
	-- build list of useable columns by excluding image, text, ntext, binary, varbinary, timestamp
		
	INSERT INTO @Columns
	  (
	    ColumnName,
	    QuotedColumnName,
	    VariableColumnName,
	    Datatype,
	    MaxLength,
	    IsIdentity,
	    IsTimestamp,
	    IsComputed,
	    IsPartOfPrimaryKey
	  )
	SELECT ColumnName,
	       QuotedColumnName,
	       VariableColumnName,
	       Datatype,
	       MaxLength,
	       IsIdentity,
	       IsTimestamp,
	       IsComputed,
	       IsPartOfPrimaryKey
	FROM   dbo.fn_ColumnsAndAttributes(@Table)
	
	SELECT @ColumnList = COALESCE(@ColumnList + ',', '') + QUOTENAME(ColumnName)
	FROM   @Columns
	WHERE  Datatype NOT IN ('image', 'text', 'ntext', 'binary', 'varbinary', 'timestamp')
	
	--SELECT @ColumnList  -- for debugging
	-------------------------------------------------------------------------------
	
	-- build dynamic sql to create trigger
	SET @SQL = 'CREATE TRIGGER [' + @Table + '_Audit_InsUpdDelTrg] ON dbo.' + @Table + @CRLF
	SET @SQL = @SQL + 'FOR INSERT, UPDATE, DELETE' + @CRLF
	SET @SQL = @SQL + 'AS' + @CRLF
	SET @SQL = @SQL + 'BEGIN' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- audit record variables' + @CRLF
	SET @SQL = @SQL + 'DECLARE @TABLE_NAME sysname' + @CRLF
	SET @SQL = @SQL + 'DECLARE @COLUMN_NAME sysname' + @CRLF
	SET @SQL = @SQL + 'DECLARE @OLD_VALUE VARCHAR(200)' + @CRLF
	SET @SQL = @SQL + 'DECLARE @NEW_VALUE VARCHAR(200)' + @CRLF
	SET @SQL = @SQL + 'DECLARE @USER_NAME sysname' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + 'DECLARE @COLUMN_COUNTER INT' + @CRLF
	SET @SQL = @SQL + 'DECLARE @COLUMN_NUMBER INT' + @CRLF
	SET @SQL = @SQL + 'DECLARE @BIT INT' + @CRLF
	SET @SQL = @SQL + 'DECLARE @CHAR INT' + @CRLF
	SET @SQL = @SQL + 'DECLARE @MAX_COLUMNS INT' + @CRLF
	SET @SQL = @SQL + 'DECLARE @SQL VARCHAR(2000)' + @CRLF
	SET @SQL = @SQL + 'DECLARE @PRIMARY_KEY_VALUES VARCHAR(200)' + @CRLF
	SET @SQL = @SQL + 'DECLARE @PRIMARY_KEY_COLUMNS VARCHAR(200)' + @CRLF
	SET @SQL = @SQL + 'DECLARE @ON_CLAUSE VARCHAR(200)' + @CRLF
	SET @SQL = @SQL + 'DECLARE @DATA_TYPE sysname' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- initialize some variables' + @CRLF
	SET @SQL = @SQL + 'SET @USER_NAME = SYSTEM_USER' + @CRLF
	SET @SQL = @SQL + 'SET @COLUMN_NUMBER = 0' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- find the table that this trigger is attached to' + @CRLF
	SET @SQL = @SQL + 'SELECT @TABLE_NAME = OBJECT_NAME(PARENT_OBJ)' + @CRLF
	SET @SQL = @SQL + '    FROM SYSOBJECTS' + @CRLF
	SET @SQL = @SQL + '    WHERE ID = @@PROCID' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- make sure the table has a primary key' + @CRLF
	SET @SQL = @SQL + '--      SEE NEXT STATEMENT, NO NEED TO DO THIS TWICE' + @CRLF
	SET @SQL = @SQL + '--IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @TABLE_NAME AND CONSTRAINT_TYPE = ''PRIMARY KEY'')' + @CRLF
	SET @SQL = @SQL + '--BEGIN' + @CRLF
	SET @SQL = @SQL + '--    RAISERROR(''No Primary Key on Table [%s].'', 16, -1, @TABLE_NAME)' + @CRLF
	SET @SQL = @SQL + '--    RETURN' + @CRLF
	SET @SQL = @SQL + '--END' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- build the on clause for the insert statement that will be built later.  this also determines if the primary key is missing' + @CRLF
	SET @SQL = @SQL + 'SELECT @ON_CLAUSE = COALESCE(@ON_CLAUSE + '' AND'', '' ON'') + '' I.'' + C.COLUMN_NAME + '' = D.'' + C.COLUMN_NAME' + @CRLF
	SET @SQL = @SQL + '    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C' + @CRLF
	SET @SQL = @SQL + '        ON C.TABLE_NAME = PK.TABLE_NAME AND C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME' + @CRLF
	SET @SQL = @SQL + '    WHERE PK.TABLE_NAME = @TABLE_NAME AND' + @CRLF
	SET @SQL = @SQL + '        CONSTRAINT_TYPE = ''PRIMARY KEY''' + @CRLF
	SET @SQL = @SQL + 'IF @ON_CLAUSE IS NULL' + @CRLF
	SET @SQL = @SQL + 'BEGIN' + @CRLF
	SET @SQL = @SQL + '    RAISERROR(''No Primary Key on Table [%s].'', 16, -1, @TABLE_NAME)' + @CRLF
	SET @SQL = @SQL + '    RETURN' + @CRLF
	SET @SQL = @SQL + 'END' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- build the list of columns used in the primary key' + @CRLF
	SET @SQL = @SQL + 'SELECT @PRIMARY_KEY_COLUMNS = COALESCE(@PRIMARY_KEY_COLUMNS + '';'', '''''''') + C.COLUMN_NAME' + @CRLF
	SET @SQL = @SQL + '    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C' + @CRLF
	SET @SQL = @SQL + '        ON C.TABLE_NAME = PK.TABLE_NAME AND C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME' + @CRLF
	SET @SQL = @SQL + '    WHERE PK.TABLE_NAME = @TABLE_NAME AND' + @CRLF
	SET @SQL = @SQL + '        CONSTRAINT_TYPE = ''PRIMARY KEY''' + @CRLF
	SET @SQL = @SQL + 'SET @PRIMARY_KEY_COLUMNS = @PRIMARY_KEY_COLUMNS + ''''''''' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- see how many columns are in the table' + @CRLF
	SET @SQL = @SQL + 'SELECT @MAX_COLUMNS = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '-- save the changing records' + @CRLF
	SET @SQL = @SQL + 'SELECT ' + @ColumnList + ' INTO #INS FROM INSERTED' + @CRLF
	SET @SQL = @SQL + 'SELECT ' + @ColumnList + ' INTO #DEL FROM DELETED' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + 'select * from #INS  -- for debugging'
	SET @SQL = @SQL + 'select * from #DEL  -- for debugging'
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + 'WHILE @COLUMN_NUMBER < @MAX_COLUMNS' + @CRLF
	SET @SQL = @SQL + 'BEGIN' + @CRLF
	SET @SQL = @SQL + '    -- this funky approach handles more than the normal 32 column limit of UPDATE(column)' + @CRLF
	SET @SQL = @SQL + '    SELECT @COLUMN_NUMBER = MIN(ORDINAL_POSITION) ' + @CRLF
	SET @SQL = @SQL + '        FROM INFORMATION_SCHEMA.COLUMNS ' + @CRLF
	SET @SQL = @SQL + '        WHERE TABLE_NAME = @TABLE_NAME AND ' + @CRLF
	SET @SQL = @SQL + '              ORDINAL_POSITION > @COLUMN_NUMBER' + @CRLF
	SET @SQL = @SQL + '    -- change bitwise column position into a readable name' + @CRLF
	SET @SQL = @SQL + '    SELECT @BIT = (@COLUMN_NUMBER - 1 ) % 8 + 1' + @CRLF
	SET @SQL = @SQL + '    SELECT @BIT = POWER(2, @BIT - 1)' + @CRLF
	SET @SQL = @SQL + '    SELECT @CHAR = ((@COLUMN_NUMBER - 1) / 8) + 1' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '    IF SUBSTRING(COLUMNS_UPDATED(), @CHAR, 1) & @BIT > 0' + @CRLF
	SET @SQL = @SQL + '    BEGIN' + @CRLF
	SET @SQL = @SQL + '        SELECT @COLUMN_NAME = QUOTENAME(COLUMN_NAME),' + @CRLF
	SET @SQL = @SQL + '               @DATA_TYPE = DATA_TYPE' + @CRLF
	SET @SQL = @SQL + '            FROM INFORMATION_SCHEMA.COLUMNS' + @CRLF
	SET @SQL = @SQL + '            WHERE TABLE_NAME = @TABLE_NAME AND' + @CRLF
	SET @SQL = @SQL + '                  ORDINAL_POSITION = @COLUMN_NUMBER' + @CRLF
	SET @SQL = @SQL + '        -- comment these two lines if you want to audit the housekeeping columns' + @CRLF
	SET @SQL = @SQL + '        --   also comment the end statement below' + @CRLF
	SET @SQL = @SQL + '		if @column_name not in (''[RevUid]'', ''[RevDt]'', ''[RevMdlName]'', ''[CreateUid]'', ''[CreateDt]'')' + @CRLF
	SET @SQL = @SQL + '		begin' + @CRLF
	SET @SQL = @SQL + '		if @data_type not in (''image'', ''text'', ''ntext'', ''binary'', ''varbinary'', ''timestamp'')' + @CRLF
	SET @SQL = @SQL + '		begin' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '        -- build concatenated primary key values as a string' + @CRLF
	SET @SQL = @SQL + '        SET @SQL = NULL' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = ''CAST(I.'' + C.COLUMN_NAME + '' AS VARCHAR(128)) '' + COALESCE(''+ '' + '''''';'''' + '' + @SQL, '''') ' + @CRLF
	SET @SQL = @SQL + '            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C' + @CRLF
	SET @SQL = @SQL + '                ON C.TABLE_NAME = PK.TABLE_NAME AND C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME' + @CRLF
	SET @SQL = @SQL + '            WHERE PK.TABLE_NAME = @TABLE_NAME AND' + @CRLF
	SET @SQL = @SQL + '                CONSTRAINT_TYPE = ''PRIMARY KEY''' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '        SELECT @PRIMARY_KEY_VALUES = RTRIM(@SQL)' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '        -- build an insert statement to enter information into the audit table' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = ''INSERT INTO AUDIT (TableName, ColumnName, OldValue, NewValue, Timestamp, UserID, PrimaryKeyValues, PrimaryKeyColumns)''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '' SELECT '''''' + @TABLE_NAME + ''''''''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', '''''' + @COLUMN_NAME + ''''''''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', CAST(D.'' + @COLUMN_NAME + '' AS VARCHAR(200))''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', CAST(I.'' + @COLUMN_NAME + '' AS VARCHAR(200))''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', GETDATE()''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', '''''' + @USER_NAME + ''''''''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', '' + @PRIMARY_KEY_VALUES + ''''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '', '' + @PRIMARY_KEY_COLUMNS + ''''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '' FROM #INS I FULL OUTER JOIN #DEL D''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + @ON_CLAUSE' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '' WHERE I.'' + @COLUMN_NAME + '' <> D.'' + @COLUMN_NAME' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '' OR (I.'' + @COLUMN_NAME + '' IS NULL AND D.'' + @COLUMN_NAME + '' IS NOT NULL)''' + @CRLF
	SET @SQL = @SQL + '        SELECT @SQL = @SQL + '' OR (I.'' + @COLUMN_NAME + '' IS NOT NULL AND D.'' + @COLUMN_NAME + '' IS NULL)''' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '        --SELECT @SQL  -- for debugging' + @CRLF
	SET @SQL = @SQL + '        EXEC(@SQL)' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '		END' + @CRLF
	SET @SQL = @SQL + '        -- comment this if you commented the lines above to audit the housekeeping columns' + @CRLF
	SET @SQL = @SQL + '		END' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + '    END' + @CRLF
	SET @SQL = @SQL + 'END' + @CRLF
	SET @SQL = @SQL + @CRLF
	SET @SQL = @SQL + 'END' + @CRLF
	
	--SELECT @SQL  -- for debugging
	EXEC (@SQL)
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not create ''' + @Table + '_Audit_InsUpdDelTrg'' trigger.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 6
	END
	
	RETURN 0
END

